from app.models import *
from sqlalchemy import or_

class Chats:
    def __init__(self):
        self.Now = datetime.datetime.now()
    def home_chats(self,type,aid,days=15):
        sql = [SellDays.aid == aid,db.cast(SellDays.createtime, db.DATE) >= self.Now - datetime.timedelta(days=days)]
        gsql = [db.func.year(SellDays.createtime), db.func.month(SellDays.createtime),db.func.day(SellDays.createtime)]

        if type not in [1,2,3,4,5] or not aid:
            return {"data":[],"date":[]}

        if type == 1:
            sql.append(or_(SellDays.call_status != None,SellDays.call_status !=0))

        if type == 2:
            sql.append(SellDays.wechat_status==2)

        if type == 3:
            sql.append(SellDays.Intent_status==1)

        if type == 4:
            sql.append(or_(SellDays.call_status == 2,SellDays.call_status==3))

        if type == 5:
            sql.append(SellDays.wechat_status ==1)



        ret = db.session.query(db.func.count(SellDays.id).label("sum"), SellDays.createtime).filter(*sql).\
            group_by(*gsql).all()

        ret = {i.createtime.strftime('%Y-%m-%d'): i.sum for i in ret}
        data = []
        date = []
        Now = datetime.datetime.now().date() - datetime.timedelta(days=days-1)
        for i in range(days):
            date.append((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            sum = ret.get((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            data.append(sum if sum else 0)

        return {"data": data, "date": date}

    def seo_chats(self,type=1,days=15):
        sql = [db.cast(User.createtime, db.DATE) >= self.Now - datetime.timedelta(days=days)]
        gsql = [db.func.year(User.createtime), db.func.month(User.createtime),db.func.day(User.createtime)]

        if type not in [1,2,3,4]:
            return {"data":[],"date":[]}

        if type == 2:
            sql.append(UserSell.source=="百度搜索")

        if type == 3:
            sql.append(UserSell.source=="360搜索")

        if type == 4:
            sql.append(UserSell.source=="搜狗搜索")




        ret = db.session.query(db.func.count(User.id).label("sum"), User.createtime).join(UserSell).filter(*sql).\
            group_by(*gsql).all()

        ret = {i.createtime.strftime('%Y-%m-%d'): i.sum for i in ret}
        data = []
        date = []
        Now = datetime.datetime.now().date() - datetime.timedelta(days=days-1)
        for i in range(days):
            date.append((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            sum = ret.get((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            data.append(sum if sum else 0)

        return {"data": data, "date": date}

    def seo_bar(self):
        ret = db.session.query(db.func.count(Visitor.id).label("sum"), Visitor.city).order_by(
            db.func.count(Visitor.id).desc()).group_by(Visitor.city).limit(7).all()
        if not ret:
            return {"data": [0], "city": ["暂无数据"]}
        data = [i.sum for i in ret]
        city = [i.city for i in ret]

        return {"data":data,"city":city}

    def seo_header_visitor(self):

        dateSql = [db.cast(Visitor.createtime, db.DATE) >= self.Now.date() - datetime.timedelta(days=1)]

        data = {
            "baidu": {
                "count": db.session.query(db.func.count(Visitor.id).label("count")).filter(Visitor.source=="百度搜索").first().count,
                "yeday": db.session.query(db.func.count(Visitor.id).label("count")).filter(*dateSql).filter(Visitor.source=="百度搜索").first().count,
            },
            "sougou": {
                "count": db.session.query(db.func.count(Visitor.id).label("count")).filter(Visitor.source=="搜狗搜索").first().count,
                "yeday": db.session.query(db.func.count(Visitor.id).label("count")).filter(*dateSql).filter(Visitor.source=="百度搜索").first().count,
            },
            "esou": {
                "count": db.session.query(db.func.count(Visitor.id).label("count")).filter(Visitor.source=="360搜素").first().count,
                "yeday": db.session.query(db.func.count(Visitor.id).label("count")).filter(*dateSql).filter(Visitor.source=="百度搜索").first().count,
            },
            "sum": {
                "count": db.session.query(db.func.count(Visitor.id).label("count")).first().count,
                "yeday": db.session.query(db.func.count(Visitor.id).label("count")).filter(*dateSql).first().count,
            }
        }

        return data

    def seo_header_register(self):

        dateSql = [db.cast(User.createtime, db.DATE) >= self.Now.date() - datetime.timedelta(days=1)]

        data = {
            "baidu": {
                "count": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="百度搜索").first().count,
                "yeday": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="百度搜索").filter(*dateSql).first().count,
            },
            "sougou": {
                "count": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="搜狗搜索").first().count,
                "yeday": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="搜狗搜索").filter(*dateSql).first().count,
            },
            "esou": {
                "count": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="360搜索").first().count,
                "yeday": db.session.query(db.func.count(User.id).label("count")).join(UserSell).filter(
                    UserSell.source=="360搜索").filter(*dateSql).first().count,
            },
            "sum": {
                "count": db.session.query(db.func.count(User.id).label("count")).first().count,
                "yeday": db.session.query(db.func.count(User.id).label("count")).filter(*dateSql).first().count,
            }
        }

        return data

    def seo_keyword_pie(self,type=1):
        color = ["#00A1D6","#F59A23","#C280FF","#D9001B"]
        sql = list()

        if type ==2:
            sql.append(Visitor.source=='百度搜索')

        if type == 3:
            sql.append(Visitor.source == '360搜索')

        if type == 4:
            sql.append(Visitor.source == '搜狗搜索')

        ret = db.session.query(db.func.count(Visitor.id).label("count"),
                               db.func.sum(db.func.IF((Visitor.islogin), 1, 0)).label("reg"),
                               Visitor.keyword).filter(*sql).group_by(
            Visitor.keyword).order_by(db.func.count(Visitor.id).desc()).limit(4).all()

        if not ret:
            return [{
                "value": 0,
                "name": "暂无数据",
                "reg": 0,
                "skip":False,
                "itemStyle": {
                    "color": "#00A1D6",
                }
            }]

        data = []
        for obj, color in zip(ret, color):
            data.append({
                "value": obj.count,
                "name": obj.keyword,
                "reg": int(obj.reg),
                "skip": True,
                "itemStyle": {
                    "color": color,
                }
            })

        return data

    def seo_source_pie(self):
        ret = db.session.query(
            db.func.count(Visitor.id).label("count"),
            db.func.sum(db.func.IF((Visitor.islogin), 1, 0)).label("reg"),
            Visitor.source).group_by(Visitor.source).all()
        #数据
        data = {
            "baidu":{
                "name":"百度",
                "value":0,
                "reg": 0
            },
            "esou": {
                "name": "360",
                "value": 0,
                "reg": 0
            },
            "sougou": {
                "name": "搜狗",
                "value": 0,
                "reg": 0
            },
            "qita": {
                "name": "其他",
                "value": 0,
                "reg":0
            }
        }
        #数据重组
        for i in ret:
            if i.source =="百度搜索":
                data["baidu"]["value"]= i.count
                data["baidu"]["reg"] = int(i.reg)
            if i.source == "360搜索":
                data["esou"]["value"] = i.count
                data["esou"]["reg"] = int(i.reg)
            if i.source == "搜狗搜索":
                data["sougou"]["value"] = i.count
                data["sougou"]["reg"] = int(i.reg)
            if i.source == "其他访客":
                data["qita"]["value"] = i.count
                data["qita"]["reg"] = int(i.reg)

        visitor = []
        register = []
        #数据重组
        for i in data.values():
            visitor.append({
                "name":i.get("name") + "访客",
                "value":i.get("value")
            })
            register.append({
                "name": i.get("name") + "注册",
                "value": i.get("reg")
            })

        return {"visitor":visitor,"register":register}

    def anal_keyword_chats(self,keyword,type=1,days=7):
        sql = [db.cast(User.createtime, db.DATE) >= self.Now - datetime.timedelta(days=days),UserSell.keyword==keyword]
        gsql = [db.func.year(User.createtime), db.func.month(User.createtime), db.func.day(User.createtime)]

        if type not in [1, 2, 3, 4]:
            return {"data": [], "date": []}

        if type == 2:
            sql.append(UserSell.source == "百度搜索")

        if type == 3:
            sql.append(UserSell.source == "360搜索")

        if type == 4:
            sql.append(UserSell.source == "搜狗搜索")

        ret = db.session.query(db.func.count(User.id).label("sum"), User.createtime).join(UserSell).filter(*sql). \
            group_by(*gsql).all()

        ret = {i.createtime.strftime('%Y-%m-%d'): i.sum for i in ret}
        data = []
        date = []
        Now = datetime.datetime.now().date() - datetime.timedelta(days=days - 1)
        for i in range(days):
            date.append((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            sum = ret.get((Now + datetime.timedelta(days=i)).strftime('%Y-%m-%d'))
            data.append(sum if sum else 0)

        return {"data": data, "date": date}
